package druid;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Test;
import pojo.Brand;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * 使用JDBC实现数据库内数据的增删查改
 */
public class Demo {
    @Test
    public void testSelectAll() throws Exception{
        /**
         * 查询所有内容,结果存入list<Brand>中
         */
        //加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("src/druid.properties"));
        //获取连接池对象
        DataSource ds = DruidDataSourceFactory.createDataSource(prop);
        //获取数据库连接
        Connection con = ds.getConnection();
        //写sql语句
        String sql = "select * from tb_brand";
        //获取pstmt对象
        PreparedStatement pstmt = con.prepareStatement(sql);

        ResultSet rs = pstmt.executeQuery();
        List<Brand> brands = new ArrayList<>();
        while(rs.next()){
            //获取数据
            int id = rs.getInt("id");
            String brandName = rs.getString("brand_name");
            String companyName = rs.getString("company_name");
            int ordered = rs.getInt("ordered");
            String description = rs.getString("description");
            int status = rs.getInt("status");
            //封装Brand对象
            brands.add(new Brand(id,brandName,companyName,ordered,description,status));
        }
        System.out.println(brands);
        rs.close();
        pstmt.close();
        con.close();
    }

    /**
     * 添加功能,需要id之外的全部信息
     */
    @Test
    public void testAdd() throws Exception{
        //接受页面的输入
        String brandName = "香飘飘";
        String companyName = "飘飘集团";
        int ordered = 1;
        String description = "绕地球一圈";
        int status = 1;

        //1.加载配置文件,获取connection
        Properties prop = new Properties();
        prop.load(new FileInputStream("src/druid.properties"));
        DataSource ds = DruidDataSourceFactory.createDataSource(prop);
        Connection con = ds.getConnection();

        String sql = "insert into tb_brand(brand_name,company_name,ordered,description,status) values(?,?,?,?,?)";
        PreparedStatement pstmt = con.prepareStatement(sql);
        //设置参数,这里因为使用预编译和转义,用户输入内容不会影响到sql语句的功能
        pstmt.setString(1,brandName);
        pstmt.setString(2,companyName);
        pstmt.setInt(3,ordered);
        pstmt.setString(4,description);
        pstmt.setInt(5,status);

        int count = pstmt.executeUpdate();

        System.out.println(count>0?"写入成功":"写入失败");

        pstmt.close();
        con.close();
    }

    @Test
    public void testUpdate() throws Exception{
        //接受页面的输入
        String brandName = "臭飘飘";
        String companyName = "优乐没";
        int ordered = 1;
        String description = "绕地球一圈";
        int status = 1;

        //1.加载配置文件,获取connection
        Properties prop = new Properties();
        prop.load(new FileInputStream("src/druid.properties"));
        DataSource ds = DruidDataSourceFactory.createDataSource(prop);
        Connection con = ds.getConnection();

        String sql = "update tb_brand set brand_name = ?,company_name = ?,ordered = ?,description = ?,status = ? where id = 4";
        PreparedStatement pstmt = con.prepareStatement(sql);
        //设置参数,这里因为使用预编译和转义,用户输入内容不会影响到sql语句的功能
        pstmt.setString(1,brandName);
        pstmt.setString(2,companyName);
        pstmt.setInt(3,ordered);
        pstmt.setString(4,description);
        pstmt.setInt(5,status);

        int count = pstmt.executeUpdate();

        System.out.println(count>0?"更新成功":"更新失败");

        pstmt.close();
        con.close();
    }

    @Test
    public void testDeleteById() throws Exception{
        int id = 4;

        //1.加载配置文件,获取connection
        Properties prop = new Properties();
        prop.load(new FileInputStream("src/druid.properties"));
        DataSource ds = DruidDataSourceFactory.createDataSource(prop);
        Connection con = ds.getConnection();

        String sql = "delete from tb_brand where id = ?";
        PreparedStatement pstmt = con.prepareStatement(sql);
        //设置参数,这里因为使用预编译和转义,用户输入内容不会影响到sql语句的功能
        pstmt.setInt(1,id);

        int count = pstmt.executeUpdate();

        System.out.println(count>0?"删除成功":"删除失败");

        pstmt.close();
        con.close();
    }
}
